﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Inventory
{
    public class PhysicalCountManager:ConnectionManager
    {
        #region Adding/Updating
        public void AddCountSheet(PhysicalCountUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "INSERT INTO OPHC (DocNum, PostingDate, WhseCode, WhseName, Remarks, DocStatus) VALUES (@DocNum, @PostingDate, @WhseCode, @WhseName, @Remarks, @DocStatus)";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@WhseCode", oUnit.WHCode));
            oCommand.Parameters.Add(new SqlParameter("@WhseName", oUnit.WHName));
            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));

            oCommand.ExecuteNonQuery();

            #region Line Items
            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;


                if (oRow["ExpiryDate"] != DBNull.Value)
                {
                    if (oRow["MfrDate"] != DBNull.Value)
                    {
                        oCommand.CommandText = "INSERT INTO PHC1 (DocNum, ItemCode, ItemName, UnitCount, UnitCost, UoM, LotNumber, MfrDate, ExpiryDate) VALUES (@DocNum, @ItemCode, @ItemName, @UnitCount, @UnitCost, @UoM, @LotNumber, @MfrDate, @ExpiryDate)";
                    }
                    else
                    {
                        oCommand.CommandText = "INSERT INTO PHC1 (DocNum, ItemCode, ItemName, UnitCount, UnitCost, UoM, LotNumber, ExpiryDate) VALUES (@DocNum, @ItemCode, @ItemName, @UnitCount, @UnitCost, @UoM, @LotNumber, @ExpiryDate)";
                    }
                }
                else
                {
                    if (oRow["MfrDate"] != DBNull.Value)
                    {
                        oCommand.CommandText = "INSERT INTO PHC1 (DocNum, ItemCode, ItemName, UnitCount, UnitCost, UoM, LotNumber, MfrDate) VALUES (@DocNum, @ItemCode, @ItemName, @UnitCount, @UnitCost, @UoM, @LotNumber, @MfrDate)";
                    }
                    else
                    {
                        oCommand.CommandText = "INSERT INTO PHC1 (DocNum, ItemCode, ItemName, UnitCount, UnitCost, UoM, LotNumber) VALUES (@DocNum, @ItemCode, @ItemName, @UnitCount, @UnitCost, @UoM, @LotNumber)";
                    }
                }
                
                
                
                
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@ItemName", oRow["ItemName"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@UnitCount", Convert.ToDouble(oRow["UnitCount"])));
                oCommand.Parameters.Add(new SqlParameter("@UnitCost", Convert.ToDouble(oRow["UnitCost"])));
                oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));
                if (oRow["ExpiryDate"] != DBNull.Value)
                {
                    oCommand.Parameters.Add(new SqlParameter("@ExpiryDate", Convert.ToDateTime(oRow["ExpiryDate"])));
                }


                if (oRow["MfrDate"] != DBNull.Value)
                {

                    oCommand.Parameters.Add(new SqlParameter("@MfrDate", Convert.ToDateTime(oRow["MfrDate"])));
                }
                oCommand.ExecuteNonQuery();
            }
            #endregion

        }

        public void UpdateCountSheet(PhysicalCountUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE OPHC SET PostingDate = @PostingDate, WhseCode=@WhseCode, WhseName=@WhseName, Remarks=@Remarks, DocStatus=@DocStatus where DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@WhseCode", oUnit.WHCode));
            oCommand.Parameters.Add(new SqlParameter("@WhseName", oUnit.WHName));
            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));

            oCommand.ExecuteNonQuery();


            #region Removing Line Items
            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE FROM PHC1 WHERE DocNum = @DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.ExecuteNonQuery();
            #endregion

            #region Line Items
            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = "INSERT INTO PHC1 (DocNum, ItemCode, ItemName, UnitCount, UnitCost, UoM, LotNumber, MfrDate, ExpiryDate) VALUES (@DocNum, @ItemCode, @ItemName, @UnitCount, @UnitCost, @UoM, @LotNumber, @MfrDate, @ExpiryDate)";
                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemName", oRow["ItemName"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UnitCount", Convert.ToDouble(oRow["UnitCount"])));
                    oCommand.Parameters.Add(new SqlParameter("@UnitCost", Convert.ToDouble(oRow["UnitCost"])));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));
                    if (oRow["ExpiryDate"] != DBNull.Value)
                    {
                        oCommand.Parameters.Add(new SqlParameter("@ExpiryDate", Convert.ToDateTime(oRow["ExpiryDate"]).ToShortDateString()));
                    }
                    else
                    {
                        oCommand.Parameters.Add(new SqlParameter("@ExpiryDate", ""));
                    }
                    if (oRow["MfrDate"] != DBNull.Value)
                    {
                        oCommand.Parameters.Add(new SqlParameter("@MfrDate", Convert.ToDateTime(oRow["MfrDate"]).ToShortDateString()));
                    }
                    else
                    {
                        oCommand.Parameters.Add(new SqlParameter("@MfrDate", ""));
                    }

                    oCommand.ExecuteNonQuery();
                }
            }
            #endregion

        }
        #endregion

        #region Changing Status
        public void ApplyToInventory(string sDocNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPHC SET DOCSTATUS ='Applied' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocNumber));
            oCommand.ExecuteNonQuery();


            SqlCommand oCommandHeaders = new SqlCommand();
            SqlDataAdapter oAdapterHeaders = new SqlDataAdapter();

            SqlCommand oCommandLineItems = new SqlCommand();
            SqlDataAdapter oAdapterLineItems = new SqlDataAdapter();
            DataSet ds = new DataSet();
            DataTable dtHeader = new DataTable();

            oCommandHeaders.Connection = this.Connection;
            oCommandHeaders.CommandText = "SELECT * FROM OPHC WHERE DocNum=@DocNum";
            oCommandHeaders.Parameters.Add(new SqlParameter("@DocNum",sDocNumber));

            oAdapterHeaders.SelectCommand = oCommandHeaders;
            oAdapterHeaders.Fill(dtHeader);

            oCommandLineItems.Connection = this.Connection;
            oCommandLineItems.CommandText = "SELECT * FROM PHC1 WHERE DoCnum=@DoCnum";
            oCommandLineItems.Parameters.Add(new SqlParameter("@DoCnum", sDocNumber));

            oAdapterLineItems.SelectCommand = oCommandLineItems;
            oAdapterLineItems.Fill(ds);

            DataTable dtLineItems = ds.Tables[0];

            foreach (DataRow oRow in dtLineItems.Rows)
            {
                StockManager oManager = new StockManager();

                oManager.Connection = this.Connection;

                if (!oManager.IsLotNumberExists(dtHeader.Rows[0]["WhseCode"].ToString(), oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString()))
                {
                    oManager.AddStocks(dtHeader.Rows[0]["WhseCode"].ToString(), dtHeader.Rows[0]["WhseName"].ToString(), oRow["ItemCode"].ToString(), oRow["ItemName"].ToString(), oRow["UnitCount"].ToString(), oRow["UoM"].ToString(), oRow["LotNumber"].ToString(), oRow["MfrDate"].ToString(), oRow["ExpiryDate"].ToString(), oRow["UnitCost"].ToString(), "BB", "Beginning Balance");
                }
                else
                {
                    oManager.UpdateStocks(dtHeader.Rows[0]["WhseCode"].ToString(), oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString(), oRow["UnitCount"].ToString(), oRow["UnitCost"].ToString());
                } 
            }




        }

        public void CancelDocument(string sDocNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPHC SET DOCSTATUS ='Canceled' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocNumber));
            oCommand.ExecuteNonQuery();
        }
        #endregion

        #region Getting Data
        public DataTable GetPhysicalCounts(string sDocNumber)
        {
            sDocNumber = '%' + sDocNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 * FROM OPHC WHERE DocNum LIKE @DOCNUM ORDER BY PostingDate DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocNumber));
            

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
        public DataTable GetPhysicalCounts(string sWhsCode, string sDocNumber)
        {
            sDocNumber = '%' + sDocNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 * FROM OPHC WHERE WhseCode=@WhseCode AND DocNum LIKE @DOCNUM ORDER BY PostingDate DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocNumber));
            oCommand.Parameters.Add(new SqlParameter("@WhseCode", sWhsCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetDocuments(string sWhsCode)
        {
            SqlCommand oCommand = new SqlCommand();
            DataTable dt = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OPHC WHERE WhseCode = @WhseCode AND DOCSTATUS='Applied' ORDER BY DOCNUM DESC";
            oCommand.Parameters.Add(new SqlParameter("@WhseCode", sWhsCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);


            return dt;
        }

        public DataRow GetDocument(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OPHC WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            DataRow oRow = ds.Tables[0].Rows[0];

            return oRow;
        }

        public DataTable GetLineItems(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM PHC1 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }

        #endregion
    }
}
